﻿CREATE PROCEDURE [util].[SetLogins](
	@LoginMetaDataXml xml
)
AS
BEGIN
	-- parse the user xml 
	DECLARE @docHandle int;
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @LoginMetaDataXml;

	-- loop through each user
	DECLARE C_LOGINS CURSOR STATIC FOR(
		SELECT
			[LoginName],
			[DefaultDatabase],
			[DefaultLanguage]
		FROM OPENXML(@docHandle, N'//Login') 
		WITH (
			[LoginName] sysname '@Name',
			[DefaultDatabase] sysname '@DefaultDatabase',
			[DefaultLanguage] sysname '@DefaultLanguage'
		)
	)
	OPEN C_LOGINS

	DECLARE @LoginName sysname, @DefaultDatabase sysname, @DefaultLanguage sysname;

	FETCH NEXT FROM C_LOGINS
		INTO @LoginName, @DefaultDatabase, @DefaultLanguage;

	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE [name] = @LoginName)
			BEGIN
				DECLARE @CreateLogin nvarchar(256);
				SET @CreateLogin = N'CREATE LOGIN [' + @LoginName + N'] FROM WINDOWS WITH DEFAULT_DATABASE=[' + @DefaultDatabase + N'], DEFAULT_LANGUAGE=[' + @DefaultLanguage + N']';
				EXEC master.dbo.sp_executesql @CreateLogin;
				
				PRINT('Login [' + @LoginName + '] created');
			END

			DECLARE @GrantConnect nvarchar(512);
			SET @GrantConnect = N'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [' + @LoginName + N']';
			EXEC master.dbo.sp_executesql @GrantConnect;

			PRINT('Login [' + @LoginName + '] granted CONNECT ON ENDPOINT::[TSQL Default TCP]');

			FETCH NEXT FROM C_LOGINS
				INTO @LoginName, @DefaultDatabase, @DefaultLanguage;
		END
	CLOSE C_LOGINS
	DEALLOCATE C_LOGINS

	-- cleanup xml
	EXEC sp_xml_removedocument @docHandle;
END